﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Dapper;
using Kugar.Core.Configuration;
using Kugar.Core.ExtMethod;
using Newtonsoft.Json.Linq;

namespace Kugar.Tools.CityToolUpdater
{
    public class MsSQLExecutor
    {
        private IDbConnection _conn = null;

        public MsSQLExecutor(string conn)
        {
            _conn=new SqlConnection();

            _conn.ConnectionString = conn;

            try
            {
                _conn.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("打开数据库链接错误");
            }
            
        }

        public void Execute(JArray data)
        {
            var type = CustomConfigManager.Default["Config:Table:Type"].ToInt();

            if (type == 0) //单容器
            {
                singleCol(data);
            }
            else
            {
                mutilCol(data);
            }
        }

        private void singleCol(JArray data)
        {
            var tablename = CustomConfigManager.Default["Config:Table:Name"].IfEmptyOrWhileSpace("base_City");

            var sql=$@"INSERT INTO {tablename} (Type,Name,ShortName,Code,ParentID,ParentCode,Code_Path,FullPathName)
                                            OUTPUT Inserted.Id
                                            VALUES(@Type,@Name,@ShortName,@Code,@ParentID,@ParentCode,@Code_Path,@FullPathName);";


            _conn.Execute(
                $@"create table [dbo].[{tablename}] (
                     Id int primary key identity(1,1),
                     Type integer not null,
                     Name nvarchar(50) not null,
                     ShortName nvarchar(50) not null,
                     Code int not null,
                     ParentID int null,
                     ParentCode int null,
                     Code_Path varchar(26) default(''),
                     FullPathName varchar(100) default('')
                    );
                    
                    CREATE INDEX index_City_Code ON {tablename} (Code);
                    CREATE INDEX index_City_Type ON {tablename} (Type);
                    CREATE INDEX index_City_ParentID ON {tablename} (ParentID);
                    CREATE INDEX index_City_ParentCode ON {tablename} (ParentCode);
                "
            );

            foreach (JObject province in data)
            {
                var provinceid=_conn.ExecuteScalar<int>(sql, new
                                              {
                                                  Type = 0,
                                                  Name = province.GetString("name"),
                                                  ShortName=province.GetString("shortname"),
                                                  Code = province.GetString("code").PadRight(6,'0'),
                                                  ParentID= (int?)null,
                                                  ParentCode="",
                                                  Code_Path= province.GetString("code").PadRight(6, '0'),
                                                  FullPathName= province.GetString("name")
                });

                Console.WriteLine($"正在插入:{province.GetString("name")}");

                var citylist = province.GetJObjectArray("sub");

                foreach (var city in citylist)
                {
                    var cityid= _conn.ExecuteScalar<int>(sql, new
                                                            {
                                                                Type = 1,
                                                                Name = city.GetString("name"),
                                                                ShortName = city.GetString("shortname"),    
                                                                Code = city.GetString("code").PadRight(6, '0'),
                                                                ParentID = provinceid,
                                                                ParentCode = province.GetString("code").PadRight(6, '0'),
                                                                Code_Path =$"{province.GetString("code").PadRight(6, '0')}_{city.GetString("code").PadRight(6, '0')}" ,
                                                                FullPathName =(province.GetString("name")==city.GetString("name"))?city.GetString("name"):$"{province.GetString("name")}{city.GetString("name")}" //直辖市的值显示一个名字
                    });

                    Console.WriteLine($"正在插入:{province.GetString("name")}-{city.GetString("name")}");

                    foreach (var district in city.GetJObjectArray("sub"))
                    {
                        var parentFullName = (province.GetString("name") == city.GetString("name"))
                            ? city.GetString("name")
                            : $"{province.GetString("name")}{city.GetString("name")}";

                        var distractid = _conn.ExecuteScalar<int>(sql, new
                                                                    {
                                                                        Type = 2,
                                                                        Name = district.GetString("name"),
                                                                        ShortName = district.GetString("shortname"),
                                                                        Code = district.GetString("code").PadRight(6, '0'),
                                                                        ParentID = cityid.ToStringEx(),
                                                                        ParentCode = city.GetString("code").PadRight(6, '0'),
                                                                        Code_Path = $"{province.GetString("code").PadRight(6, '0')}_{city.GetString("code").PadRight(6, '0')}_{district.GetString("code").PadRight(6, '0')}",
                                                                        FullPathName=$"{parentFullName}{district.GetString("name")}"
                        });

                        Console.WriteLine($"正在插入:{province.GetString("name")}-{city.GetString("name")}-{district.GetString("name")}");
                    }
                }
            }
        }

        private void mutilCol(JArray data)
        {
            var citytablename = CustomConfigManager.Default["Config:Table:City"].IfEmptyOrWhileSpace("base_City");
            var provincetablename = CustomConfigManager.Default["Config:Table:Province"]
                .IfEmptyOrWhileSpace("base_Province");
            var districttablename = CustomConfigManager.Default["Config:Table:District"].IfEmptyOrWhileSpace("base_District");

            var provinceSQL = $@"INSERT INTO {provincetablename} (Name,Code)
                                            OUTPUT Inserted.Id
                                            VALUES(@Name,@Code);";

            var citySQL = $@"INSERT INTO {citytablename} (Name,Code,ProvinceID,ProvinceCode,Code_Path)
                                            OUTPUT Inserted.Id
                                            VALUES(@Type,@Name,@Code,@ParentID,@ParentCode,@Code_Path,@ID_Path);";

            var districtSQL = $@"INSERT INTO {districttablename} (Type,Name,Code,CityID,CityCode,Code_Path)
                                            OUTPUT Inserted.Id
                                            VALUES(@Type,@Name,@Code,@CityID,@CityCode,@Code_Path);";


            _conn.Execute(
                $@"create table [dbo].[{provincetablename}] (
                     Id int primary key identity(1,1),
                     Name nvarchar(100) not null,
                     Code int not null
                    );

                    CREATE UNIQUE INDEX index_{provincetablename}_Code ON {provincetablename} (Code);
                    "
            );

            _conn.Execute(
                $@"create table [dbo].[{citytablename}] (
                     Id int primary key identity(1,1),
                     Name nvarchar(100) not null,
                     Code int not null,
                     ProvinceID int not null,
                     ProvinceCode int not null,
                     Code_Path varchar(26) default('')
                    );

                    CREATE UNIQUE INDEX index_City_Code ON {citytablename} (Code);
                    CREATE INDEX index_City_ProvinceID ON {citytablename} (ProvinceID);
                    CREATE INDEX index_City_ProvinceCode ON {citytablename} (ProvinceCode);
"
            );

            _conn.Execute(
                $@"create table [dbo].[{districttablename}] (
                     Id int primary key identity(1,1),
                     Name nvarchar(100) not null,
                     Code int not null,
                     ProvinceID int not null,
                     ProvinceCode int not null,
                     CityID int not nulll,
                     CityCode int not null,
                     Code_Path varchar(26) default('')
                    );
                    CREATE UNIQUE INDEX index_District_Code ON {districttablename} (Code);
                    CREATE INDEX index_District_ProvinceID ON {districttablename} (ProvinceID);
                    CREATE INDEX index_District_ProvinceCode ON {districttablename} (ProvinceCode);
                    CREATE INDEX index_District_CityID ON {districttablename} (CityID);
                    CREATE INDEX index_District_CityCode ON {districttablename} (CityCode);
                    "
            );

            foreach (JObject province in data)
            {
                var provinceid = _conn.ExecuteScalar<int>(provinceSQL, new
                                                               {
                                                                   Type = 0,
                                                                   Name = province.GetString("name"),
                                                                   Code = province.GetString("code").PadRight(6, '0')
                });
                
                Console.WriteLine($"正在插入:{province.GetString("name")}");

                var citylist = province.GetJObjectArray("sub");

                foreach (var city in citylist)
                {
                    var cityid = _conn.ExecuteScalar<int>(citySQL, new
                                                               {
                                                                   Name = city.GetString("name"),
                                                                   Code = city.GetString("code").PadRight(6, '0'),
                                                                   ProvinceID = provinceid.ToStringEx(),
                                                                   ProvinceCode = province.GetString("code").PadRight(6, '0'),
                                                                   Code_Path = $"{province.GetString("code").PadRight(6, '0')}_{city.GetString("code").PadRight(6, '0')}"
                                                               });
                    
                    Console.WriteLine($"正在插入:{province.GetString("name")}-{city.GetString("name")}");

                    foreach (var district in city.GetJObjectArray("sub"))
                    {
                        var distractid = _conn.ExecuteScalar<int>(districtSQL, new
                                                                       {
                                                                           Name = district.GetString("name"),
                                                                           Code = district.GetString("code").PadRight(6, '0'),
                                                                           ProvinceID= provinceid,
                                                                           ProvinceCode= province.GetString("code").PadRight(6, '0'),
                                                                           CityID = cityid.ToStringEx(),
                                                                           CityCode = city.GetString("code").PadRight(6, '0'),
                                                                           Code_Path = $"{province.GetString("code").PadRight(6, '0')}_{city.GetString("code").PadRight(6, '0')}_{district.GetString("code").PadRight(6, '0')}"
                                                                       });

                    
                        Console.WriteLine($"正在插入:{province.GetString("name")}-{city.GetString("name")}-{city.GetString("name")}");
                    }
                }
            }
        }
    }
}
